package com.simoniu.spt.course.repository;

import com.simoniu.spt.course.entity.Students;
import com.simoniu.spt.course.view.StudentsViewInterface;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface StudentsRepository extends JpaRepository<Students,Integer>, JpaSpecificationExecutor {

    //自定义根据学生的姓名和性别查询学生集合
    List<Students> findAllBySnameAndGender(String sname, String gender);

    //根据学生的性别查询并返回按照出生日期降序排序的集合
    List<Students> findByGenderOrderByBirthdayDesc(String gender);

    @Query(nativeQuery = true,
            value="select sid, sname,gender,birthday,school,major,s.cid ,cname from students s inner join class_room c where s.cid=c.cid")
    List<StudentsViewInterface> queryStudentsView();


    @Query(nativeQuery = true,
            value="select sid, sname,gender,birthday,school,major,s.cid ,cname from students s inner join class_room c where s.cid=c.cid and s.sid = :sid")

    //查询指定学生的视图
    StudentsViewInterface queryStudentsViewBySid(@Param("sid") int sid);

}
